 
  

 






<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> 
<html>

<!-- Mirrored from www.javapractices.com/topic/TopicAction.do;jsessionid=4FCCB481C702D708A7360133D128E359?Id=239 by HTTrack Website Copier/3.x [XR&CO'2010], Sun, 12 Jun 2011 17:27:36 GMT -->
<!-- Added by HTTrack --><meta http-equiv="content-type" content="text/html;charset=UTF-8"><!-- /Added by HTTrack -->
<head>
 <title>
  Java Practices -> Implement code tables
 </title>
 <link rel="stylesheet" type="text/css" href="../stylesheet8.css" media="all">
 
 <link rel="shortcut icon" href='../images/favicon.ico' type="image/vnd.microsoft.icon">
 <meta name="description" content="Concise presentations of java programming practices, tasks, and conventions, amply illustrated with syntax highlighted code examples.">
 
 <meta name='keywords' content='java,java programming,java practices,java idiom,java style,java design patterns,java coding conventions,'>
 
 
</head>
 
<body>


<div class='menu-bar'>
 
  <a href='../home/HomeAction.html' title='Table of Contents'>Home</a> |
  <a href='../vote/VoteSummaryAction-2.html' title='View Poll Results'>Poll</a> |
   
  <A href='../feedback/FeedbackAction451f-2.html?Operation=Show' title='Send Your Feedback'>Wiki</a> |
  <b><a href='../source/SourceAction-2.html' title='Grab Source Code'>Source Code</a></b><IMG class='no-margin' SRC="../images/goldstar.gif" ALT=""> |

  <a href='http://www.web4j.com/Java_Web_Application_Framework_Overview.jsp?From=1' title='Free Download - Java Web Application Framework'><b>WEB4J</b></a> |
  
  <a href='http://www.date4j.net/' title='Replacement for java.util.Date'><b>DATE4J</b></a> |

   <a href='../references/ReferencesAction-2.html' title='References'>Links</a>
   
  <form action='http://www.javapractices.com/search/SearchAction.do' method='get' class='search-form'>
   <input type='text' name='SearchTerms' value="" size=12 maxlength=50 class='search'>
   <input type='submit' value="Search">
  </form>
 
</div>

<P>



  

 






<p class="display-messages">

 

 

</p>


<div class="main-layout">
 
   

 




<div class='page-title'>Implement code tables</div>

<div class='main-body'>
 
<br>Most applications use <i>code tables</i>. 
A reasonable definition of a code table might be : 

<P><i>A code table is a list of closely related items, each of which has minimal substructure.</i>

<P>Examples :
<ul>
 <li>a list of countries (or other geographical category)
 <li>the list of credit cards accepted by a web site
 <li>the number of items shown to the user in a search result
</ul>


<P>Code tables are often presented in drop down lists, as in :

<P><form action="http://www.javapractices.com/topic/TopicAction.do?Id=239" method="GET"> 
 <table border='1' cellpadding='3' cellspacing='0'>
  <tr><td>Number of Results: </td>
   <td>
    <select name="NumResults">
     <option>10</option>
     <option>50</option>
     <option>100</option>
    </select>
   </td>
 </tr>
  <tr><td>Flavor of Ice Cream: </td>
   <td>
    <select name="Flavor">
     <option value='1'>Chocolate</option>
     <option value='2'>Strawberry</option>
     <option value='3'>Vanilla</option>
    </select>
   </td>
 </tr>
 </table>
</form>

<P>(Radio buttons or other presentation styles may also be appropriate, according to the needs of each case.)


<P><b>Code Table Structure</b><br>
Most applications use a relational database.
Code tables usually represent the simplest kinds of tables that you can model in a database.
Here are some example code tables, as defined by SQL <tt>CREATE TABLE</tt> statements : 

<P>Number of items shown in a search result :
<PRE>CREATE TABLE NumResults (  
  Id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  NumItems TINYINT UNSIGNED UNIQUE NOT NULL, 
  PRIMARY KEY (Id)
) TYPE=InnoDB;</PRE>


<P>List of countries supported by an application :
<PRE>CREATE TABLE Country (
  Id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
  Text VARCHAR(50)UNIQUE NOT NULL,
  ShortText VARCHAR(2)UNIQUE NOT NULL,
  PRIMARY KEY (Id)
) TYPE=InnoDB;</PRE>

<P>The same list of countries, but with a field added to control sort order :
<PRE>CREATE TABLE Country (
  Id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
  OrderWith MEDIUMINT UNIQUE UNSIGNED NOT NULL,
  Text VARCHAR(50)UNIQUE NOT NULL,
  ShortText VARCHAR(2)UNIQUE NOT NULL,
  PRIMARY KEY (Id)
) TYPE=InnoDB;</PRE>

<P>It's often useful to picture each row in a code table as representing a set of <i>aliases</i> for a single idea. 
One alias can be more appropriate than another, according to the context in which it's used.
For example, in a report which tries to squeeze as much information as possible onto pages of fixed width, short abbreviations are often useful.
When presenting a drop-down list to an end user, it may be more desirable to show a longer description, instead of an abbreviation. 

<P>So, you may decide to represent an item in a code table using what amounts to several <i>aliases</i> for the same item :
<ul>
 <li>a numeric id
 <li>the 'regular' text, usually seen by the user
 <li>a short abbreviation used when appropriate
 <li>some other alias appropriate to a given case
</ul>

<P>For this idea to make sense, each of the above fields would need a <tt>UNIQUE</tt> constraint, 
and each would need to be non-null.


<P><b>Code Table Evolution</b><br>
As shown above, code tables don't have a specific, definitive structure. 
You aren't locked into a specific style. 
A single application often has many code tables, but those code tables don't necessarily share the exact same form.
Also, it's not uncommon for a code table to start its life in a simple form, and then later grow into something more elaborate. 
In this sense, code tables are roughly similar to Java's <a href='TopicAction2161-2.html?Id=1'>enumeration types</a>,  
which can start out being very simple. 
Since enumerations are also classes, you can add more structure to them later, if needed.


<P><b>In-Memory Caching</b><br>
Code tables often represent relatively static data. 
Since the data doesn't change very often, it usually makes sense to consider reading in all code tables once upon startup. 
Then, each time a code table is needed, the in-memory representations of the code tables are referenced directly, instead of repeatedly going back to the database.
This usually improves application performance.

<P><b>In-Memory Joins</b><br>
If code tables are cached in memory after startup, then you will often be able to move logic formerly implemented by a database <tt>JOIN</tt> operation into in-memory operations in Java instead.
(This is an exception to the rule of <a href='TopicAction7bff-2.html?Id=106'>not performing database tasks in code</a>.)
There are two advantages to this :
<ul>
 <li>it will likely improve performance.
 <li>your SQL statements will be a bit simpler, since the <tt>JOIN</tt>s can be left out
</ul>

For illustration, consider an application that models a team sport, in which each player on a team is assigned a specific <i>position</i> of some sort (bowler, pitcher, fullback, or whatever). 
In this case, the Team table has a foreign key into the Position code table. 
To retrieve the list of players on a team, you might have an explicit join to the Position code table, as in :

<PRE>SELECT 
 Name, Number, PositionName
FROM 
 Team JOIN Position ON PositionFK = Position.Id
WHERE
 Team.Id = ?</PRE>

Alternatively, the JOIN might be dropped in favor of returning the 'raw' PositionFK identifier, instead of the PositionName text, as in :
<PRE>SELECT 
 Name, Number, PositionFK
FROM 
 Team
WHERE 
 Team.Id = ?</PRE>

<P>Of course, the PositionFK identifier would need to be translated into text (in Java-land) before presenting the result to the user. 


<P><b>Id For Code, Text For User</b><br>
The fact that a code table item is essentially a collection of aliases for a single idea can be put to use in the following way. 
In Java-land, it's best to identify items using internal, static codes, instead of the text visible to the user. 
Forms in web applications are a good example of this :

<PRE>&lt;select name="Flavor"&gt;
  &lt;option value='1'&gt;Chocolate&lt;/option&gt;
  &lt;option value='2'&gt;Strawberry&lt;/option&gt;
  &lt;option value='3'&gt;Vanilla&lt;/option&gt;
&lt;/select&gt;</PRE>

Here, the text ('Chocolate') is shown to the user in a drop-down, but the value submitted to the server is actually a numeric id ('1'), as controlled by the <tt>value</tt> attribute of the <tt>option</tt> tag.
This separates two things nicely.
The text may change for many reasons (change the spelling, add translations into another language), but such changes will not ripple into Java, since Java-land uses numeric codes, not the text.
(Some object to exposing database primary keys to the end user like this. But in this case, it doesn't seem to do any harm.)

<P><b>Sorting</b><br>
Sorting of code tables can be tricky. 
If the sort is alphabetical, then that's simple to implement.
However, sorts aren't always alphabetical. Sometimes they depend on arbitrary rules. 
For example, if an application is used only in Australia, a list of countries having Australia and New Zealand at the beginning may be required.
In such cases, one option is to define a column in the underlying code table which explicitly defines the sort order. 

<P>The sort order can also be affected by whether an application is multilingual. 
If a code table is sorted alphabetically in each language, then the order of presentation to the user will usually not be the same in all languages.

<P><b>Monster Code Tables</b><br>
Some applications put all of their code tables into a single "monster" code table. 
The monster code table has two primary keys -- one to identify the code table, and one to identify the value  <i>within</i> the code table.
This seems to be an inferior design :
<ul>
 <li>it lumps together items which aren't logically related to each other. It's almost always a mistake to <i>put implementation details before meaning</i>. That's a bad sign.
 <li>foreign keys to code tables are no longer possible. Since foreign keys are the heart and soul of a relational database, this is a major drawback.
 <li>since some foreign keys are absent, the clarity of a database's structure (and related SQL statements) is significantly reduced.
 <li>when a single code table has special needs, then it usually can't fit into the structure of the monster code table. 
 <li>code tables can't evolve independently and still remain in the monster code table.
 <li>JOINs to monster code tables are usually doubled. What used to be a single join becomes 2 joins -- one join to define the code table, another to define the value <i>within</i> the code table.
 <li>in large SQL statements, the large number of joins can rapidly become annoying.
</ul>


<br>
<br>

</div>




<div class='topic-section'>See Also :</div>
<div class='main-body'>
 
  
  <a href='TopicAction7bff-2.html?Id=106'>Do not perform database tasks in code </a> <br>
 
</div>


<div class='topic-section'>Would you use this technique?</div>
<div class='main-body'>
  
  <form action="http://www.javapractices.com/vote/AddVoteAction.do" method='post'>
    Yes<input type='radio' name='Choice' value='Y' >
    &nbsp;&nbsp;No<input type='radio' name='Choice' value='N'>
    &nbsp;&nbsp;Undecided<input type='radio' name='Choice' value="?" >
    &nbsp;&nbsp;<input type=submit value="Vote" >
    <input type='hidden' name='Operation' value='Apply'>
    <input type='hidden' name='TopicId' value='239'>
  </form>
</div>

<div style='height:10.0em;'></div>

 
 
</div>

  

 





<div align='center' class='legalese'>  
&copy; 2011 Hirondelle Systems |
<a href='../source/SourceAction-2.html'><b>Source Code</b></a><IMG class='no-margin' SRC="../images/goldstar.gif" ALT=""> |
<a href="mailto:webmaster@javapractices.com">Contact</a> |
<a href="http://creativecommons.org/licenses/by-nc-sa/1.0/">License</a> |
<a href='../apps/cjp.rss'>RSS</a>
<!-- ukey="2AC36CD2" -->
<!-- ckey="16DF3D87" -->
<br>

 Individual code snippets can be used under this <a href='../LICENSE.txt'>BSD license</a> - Last updated on June 6, 2010.<br>
 Over 150,000 unique IPs last month - <span title='Java Practices 2.6.5, Mon May 16 00:00:00 EDT 2011'>Built with</span> <a href='http://www.web4j.com/'>WEB4J</a>.<br>
 - In Memoriam : Bill Dirani -
</div>

<script src="../../www.google-analytics.com/urchin.js" type="text/javascript">
</script>
<script type="text/javascript">
_uacct = "UA-2633428-1";
urchinTracker();
</script>



</body>

<!-- Mirrored from www.javapractices.com/topic/TopicAction.do;jsessionid=4FCCB481C702D708A7360133D128E359?Id=239 by HTTrack Website Copier/3.x [XR&CO'2010], Sun, 12 Jun 2011 17:27:36 GMT -->
<!-- Added by HTTrack --><meta http-equiv="content-type" content="text/html;charset=UTF-8"><!-- /Added by HTTrack -->
</html>
